-- Create the site map node table

CREATE TABLE [dbo].[SiteMap] (
    [ID]          [int] NOT NULL,
    [Title]       [varchar] (32),
    [Description] [varchar] (512),
    [Url]         [varchar] (512),
    [Roles]       [varchar] (512),
    [Parent]      [int]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SiteMap] ADD 
    CONSTRAINT [PK_SiteMap] PRIMARY KEY CLUSTERED 
    (
        [ID]
    )  ON [PRIMARY] 
GO

-- Add site map nodes

INSERT INTO SiteMap (ID, Title, Description, Url, Roles, Parent)
VALUES (1, 'Home', NULL, '~/Default.aspx', NULL, NULL)

INSERT INTO SiteMap (ID, Title, Description, Url, Roles, Parent)
VALUES (10, 'News', NULL, NULL, '*', 1)

INSERT INTO SiteMap (ID, Title, Description, Url, Roles, Parent)
VALUES (11, 'Local', 'News from greater Seattle', '~/Summary.aspx?CategoryID=0', NULL, 10)

INSERT INTO SiteMap (ID, Title, Description, Url, Roles, Parent)
VALUES (12, 'World', 'News from around the world', '~/Summary.aspx?CategoryID=2', NULL, 10)

INSERT INTO SiteMap (ID, Title, Description, Url, Roles, Parent)
VALUES (20, 'Sports', NULL, NULL, '*', 1)

INSERT INTO SiteMap (ID, Title, Description, Url, Roles, Parent)
VALUES (21, 'Baseball', 'What''s happening in baseball', '~/Summary.aspx?CategoryID=3', NULL, 20)

--...

-- Create the stored proc used to query site map nodes

CREATE PROCEDURE proc_GetSiteMap AS
    SELECT [ID], [Title], [Description], [Url], [Roles], [Parent]
    FROM [SiteMap] ORDER BY [ID]
GO

